package business.database
import java.util.Collection;

import business.database.Database;
import business.scripts.*;
import groovy.sql.GroovyRowResult

class BasicQuerys {

	static Integer selectMaxidWord(){
		return selectMaxid("idWord","Word")
	}
	
	static Integer selectMaxidGroupedWord(){
		return selectMaxid("idgroup","GroupedWord")
	}
	
	static Integer selectMaxidSemantic(){
		return selectMaxid("idgroupsemantic","GroupSemantic")
	}
	
	static Integer selectMaxidPreCodeCorp(){
		String query = "Select max(idcodecorp) as MAXID from PreCodeCorp"
		def maxid = Database.getInstance().sqlnewcorps.firstRow(query)
		if ((maxid != null) && (maxid[0] != null))
			return maxid[0]
		else return 0
	}
	
	/**
	 * Obtiene el identificador maximo de la tabla corp
	 * @return
	 */
	static Integer selectMaxidCorp(){
		return selectMaxid("idCorp","Corp")
	}
	
	/**
	 * Obtiene el identificador del grupo principal que define el pais
	 * @return
	 */
	static Integer selectCountryIdGroup() {
		GroovyRowResult row = Database.getInstance().sqlite.firstRow("select gw.idword, gw.idgroup, count(*) as cnt from groupedword gw inner join corpgroup cg on gw.idgroup = cg.idgroup group by cg.idgroup order by cnt desc")
		
		if (row == null) 
			return -1
		else return row[1]
	}
	
	/**
	* Obtiene el identificador de la palabra que define el pais
	* @return
	*/
   static Integer selectCountryIdWord() {
	   GroovyRowResult row = Database.getInstance().sqlite.firstRow("select gw.idword, gw.idgroup, count(*) as cnt from groupedword gw inner join corpgroup cg on gw.idgroup = cg.idgroup group by cg.idgroup order by cnt desc")
	   
	   if (row == null)
		   return -1
	   else return row[0]
   }
	
   /**
   * Obtiene el identificador de la palabra que mas define una provincia
   * @return
   */
  static List<Integer> selectProvIdWord(String prov) {
	  List<Integer> list = new ArrayList<Integer>();
	  int max = -1;
	  GroovyRowResult row = Database.getInstance().sqlite.eachRow("select gw.idword, count(*) as cnt from groupedword gw inner join corpgroup cg on gw.idgroup = cg.idgroup inner join centers_corps cc on cg.idcorp = cc.idcorp inner join center cen on cen.idce = cc.idce where cg.type = -2 and cen.provincia = ${prov} group by cg.idgroup order by cnt desc", {
		  
		  int idword = it[0]
		  int cnt = it[1]
		  if (max < 0) max = cnt;
		  
		  if (max/4 < cnt) list.add(idword); 
		  
	  })
	  
	  return list;
  }
   
	
	private static Integer selectMaxid(String col, String table) {
		String query = "Select max(${col}) as MAXID from ${table}"
		def maxid = Database.getInstance().sqlite.firstRow(query)
		if ((maxid != null) && (maxid[0] != null))
			return maxid[0]
		else return 0
	}
	
	/**
	 * Obtiene el idCorp para el corp
	 * @param corp
	 * @return idCorp o null si no lo encuentra
	 */
	static Integer selectIdCorp(String corp) {
		String query = "Select idCorp from Corp where corp = '${corp}'"
		def id = Database.getInstance().sqlite.firstRow(query)
		if ((id != null) && (id[0] != null))
			return id[0]
		else return null
	}


	
	
}
